
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE Blue_Pumpkin
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE sp_fillEventsDataTable 
AS
BEGIN
SELECT a.EventId,a.Name, a.[Description],a.EventType,a.TimeStart,a.TimeEnd,COUNT(b.Username) AS Registered
FROM EventLibrary AS a LEFT JOIN EventRegistration AS b
ON a.EventId = b.EventId
GROUP BY a.EventId,a.Name, a.[Description],a.EventType,a.TimeStart,a.TimeEnd,b.Username
ORDER BY a.TimeStart DESC
END
GO

CREATE PROCEDURE sp_testParameter
@name varchar(50)
AS
BEGIN
SELECT *FROM EventLibrary AS a
WHERE a.Name LIKE '%'+@name+'%'
END
GO

CREATE PROCEDURE sp_fillRegisteredTable
AS
BEGIN
SELECT b.Name,a.Username,a.TimeRegisted 
FROM EventRegistration AS a INNER JOIN EventLibrary AS b
ON a.EventId = b.EventId
GROUP BY b.Name,a.Username,a.TimeRegisted,a.EventId,b.EventId
ORDER BY a.TimeRegisted DESC
END
GO

CREATE PROCEDURE sp_TotalRegistered
AS
BEGIN
SELECT TOP 10 a.Username,COUNT(a.Username) AS Total
FROM EventRegistration AS a
GROUP BY a.Username
ORDER BY Total DESC
END
GO

CREATE PROCEDURE sp_getEventContentById
@eventID int
AS
BEGIN
SELECT a.EventId,a.Name,a.[Description],a.EventType,a.TimeStart,a.TimeEnd,COUNT(b.EventId) AS Comments
FROM EventLibrary AS a LEFT JOIN EventComment AS b
ON a.EventId = b.EventId
WHERE a.EventId = @eventID
GROUP BY a.EventId,a.Name,a.[Description],a.EventType,a.TimeStart,a.TimeEnd,b.EventId
END
GO
CREATE PROCEDURE sp_getEventInfoContentById
@eventId int
AS
BEGIN
SELECT a.AvatarImg,a.EventShortDesc,a.Created,a.[Address]
FROM EventInfo AS a
WHERE a.EventId = @eventId
END
GO

CREATE PROCEDURE sp_createNewEvent
@name nvarchar(200),
@des nvarchar(MAX),
@type nvarchar(100),
@timeS smalldatetime,
@timeE smalldatetime,
@avatar nvarchar(200),
@shortdes nvarchar(200),
@address nvarchar(200)
AS
BEGIN
DECLARE @key int
BEGIN TRAN
INSERT INTO EventLibrary(Name, [Description], EventType, TimeStart, TimeEnd)
VALUES(@name,@des,@type,@timeS,@timeE)
IF(@@ERROR <> 0)
        BEGIN
        ROLLBACK TRAN
        RETURN 0         
        END     
SELECT @key = @@IDENTITY
INSERT INTO EventInfo(EventId,AvatarImg,EventShortDesc,[Address])
VALUES(@key,@avatar,@shortdes,@address)
IF(@@ERROR <> 0)
        BEGIN
        ROLLBACK TRAN
        RETURN 0         
        END     
COMMIT TRAN
RETURN 1
END
GO

CREATE PROCEDURE sp_updateEventByEventId
@eventId int,
@name nvarchar(200),
@des nvarchar(MAX),
@type nvarchar(100),
@timeS smalldatetime,
@timeE smalldatetime,
@avatar nvarchar(200),
@shortdes nvarchar(200),
@address nvarchar(200)
AS
BEGIN
BEGIN TRAN
UPDATE EventLibrary SET
        Name = @name,
        [Description]= @des,
        EventType = @type,
        TimeStart = @timeS,
        TimeEnd = @timeE
WHERE EventId = @eventId
IF(@@ERROR <> 0)
        BEGIN
        ROLLBACK TRAN
        RETURN 0         
        END     
UPDATE EventInfo SET
        AvatarImg = @avatar,
        EventShortDesc = @shortdes,
        Created = GETDATE(),
        [Address] = @address
WHERE EventId = @eventId
IF(@@ERROR <> 0)
        BEGIN
        ROLLBACK TRAN
        RETURN 0         
        END
INSERT INTO Notifications(NotificationTypeID, UsernameME, EventId, [Status])
        SELECT
        6 AS NotificationTypeID, a.Username AS UsernameME, @eventId AS EventId,'N' AS [Status]
        FROM EventRegistration AS a
WHERE a.EventId = @eventId
IF(@@ERROR <> 0)
        BEGIN
        ROLLBACK TRAN
        RETURN 0         
        END
COMMIT TRAN
RETURN 1
END
GO

CREATE PROCEDURE sp_getUpdateEventInformationById
@eventId int
AS
BEGIN
SELECT a.EventId,a.Name,a.[Description],a.EventType,a.TimeStart,a.TimeEnd,b.AvatarImg,b.EventShortDesc,b.Created,b.[Address] 
FROM EventLibrary AS a INNER JOIN EventInfo AS b
ON a.EventId = b.EventId
WHERE a.EventId = @eventId
END
GO

CREATE PROCEDURE sp_createNewEmployee
@username varchar(50),
@password varchar(50),
@email varchar(50),
@fullname nvarchar(50),
@gender nvarchar(20),
@birthday smalldatetime,
@phone nvarchar(20),
@mobile nvarchar(20),
@address nvarchar(100),
@indentitycard varchar(20),
@position nvarchar(100),
@department nvarchar(100),
@groups nvarchar(200),
@avatar nvarchar(200)
AS
BEGIN
BEGIN TRAN
INSERT INTO Employee(Username, [Password],Email,Fullname,Gender,Birthdate,Phone,Mobilephone,[Address],IdentityCard,Position,Departments,[Groups],Avatar)
VALUES(@username,@password,@email,@fullname,@gender,@birthday,@phone,@mobile,@address,@indentitycard,@position,@department,@groups,@avatar)
IF(@@ERROR <> 0)
        BEGIN
        ROLLBACK TRAN
        RETURN 0         
        END
COMMIT TRAN
RETURN 1
END
GO
CREATE PROCEDURE sp_changeEmployeePassword
@username varchar(50),
@password varchar(50)
AS
BEGIN
BEGIN TRAN
UPDATE Employee SET [Password]=@password WHERE Username = @username
IF(@@ERROR <> 0)
        BEGIN
        ROLLBACK TRAN
        RETURN 0         
        END
COMMIT TRAN
RETURN 1
END
GO

CREATE PROC sp_getNewMessage
AS
BEGIN
SELECT *FROM ContactMessage WHERE Recieved = 0 ORDER BY TimeSent DESC
END
GO
CREATE PROC sp_activeMessageRead
@Id int
AS
BEGIN
SELECT *FROM ContactMessage AS a WHERE a.Username = (SELECT b.Username FROM ContactMessage AS b WHERE Id = @Id) AND a.Recieved = 1
END
GO

CREATE PROC sp_updateRecievedMessageRead
@Id int
AS
BEGIN
BEGIN TRAN
UPDATE ContactMessage SET Recieved = 1 WHERE Id = @Id
IF(@@ERROR <> 0)
     BEGIN
	 ROLLBACK TRAN
	 RETURN 0              
     END
COMMIT TRAN
RETURN 1
END
GO

CREATE PROCEDURE sp_getEventComming 
@day int
AS
BEGIN
SELECT a.EventId,a.Name,a.[Description],a.EventType,a.TimeStart,a.TimeEnd,b.AvatarImg,b.EventShortDesc,b.Created,b.[Address],COUNT(c.EventId) AS Registered
FROM EventLibrary AS a INNER JOIN EventInfo As b ON a.EventId = b.EventId
LEFT JOIN EventRegistration AS c ON a.EventId = c.EventId
WHERE DATEDIFF(DAY,GETDATE (),a.TimeStart) >= @day
GROUP BY a.EventId,a.Name,a.[Description],a.EventType,a.TimeStart,a.TimeEnd,b.AvatarImg,b.EventShortDesc,b.Created,b.[Address],c.EventId
ORDER BY DATEDIFF(DAY,GETDATE (),a.TimeStart) ASC
END
GO
CREATE PROCEDURE sp_getRegisters
@eventId int
AS
BEGIN
SELECT a.Username,a.Fullname,a.Avatar,a.Position,a.Departments,a.Logged
FROM Employee AS a INNER JOIN  EventRegistration AS b
ON a.Username = b.Username
WHERE b.EventId = @eventId
GROUP BY a.Username,a.Fullname,a.Avatar,a.Position,a.Departments,a.Logged,b.EventId
END
GO

CREATE PROCEDURE sp_getNewNotificationComment
AS
BEGIN
SELECT COUNT(a.Id) As amount
FROM EventComment AS a
WHERE DATEDIFF(DAY,a.Commented,GETDATE ()) <= 2
END
GO
CREATE PROCEDURE sp_getNewNotificationRegistration
AS
BEGIN
SELECT COUNT(a.Id) AS amount
FROM EventRegistration AS a
WHERE DATEDIFF(DAY,a.TimeRegisted,GETDATE ()) <= 2
END
GO

-- =============================================
-- Author:		<NGUYEN VAN TIEP>
-- Create date: <2014/09/04 23:24:00>
-- Description:	<Get Deatail event libary by param eventId>
-- =============================================
GO
CREATE PROCEDURE sp_getEventLibraryDetailsById
	@eventId int
AS
BEGIN
	SELECT * FROM EventLibrary 
	WHERE EventId=@eventId
END
GO

CREATE PROCEDURE sp_TotalEventType
AS
BEGIN
SELECT a.EventType, COUNT(a.EventType) AS Total
FROM EventLibrary AS a
GROUP BY a.EventType
END
GO

CREATE PROCEDURE sp_readEventComments
AS
BEGIN
SELECT  a.EventId,a.Name,b.Username,b.Comment,b.Commented
FROM EventLibrary AS a INNER JOIN EventComment AS b
ON a.EventId = b.EventId
END
GO

CREATE  PROCEDURE sp_getEventLibraryPaging
	@beginRow int,
	@endRow int,
	@Username nvarchar(50)
	AS
	BEGIN
		SELECT ev.EventId
			  ,ev.Name
			  ,ei.EventShortDesc
			  ,ev.EventType
			  ,ev.TimeStart
			  ,ev.TimeEnd
			  ,ei.AvatarImg
			  ,ei.Created 
			  ,Case When (Select EventRegistration.Username from EventRegistration Where EventRegistration.EventId= ev.EventId AND EventRegistration.Username=@Username) =@Username 
			   THEN 'Joined' ELSE 'Join'
			   END As [Status]
			  ,Case When DATEDIFF(DAY,(Select EventInfo.Created from EventInfo Where EventInfo.EventId= ev.EventId),GETDATE ())<2
			   THEN 'new' ELSE 'likenew'
			   END As [type]
			   ,(SELECT COUNT(*) FROM EventComment evc WHERE evc.EventId=ev.EventId) AS commentCount
			   ,(SELECT COUNT(*) FROM EventRegistration er WHERE er.EventId=ev.EventId ) AS joinedCount
			   FROM(SELECT ROW_NUMBER() OVER(ORDER BY TimeStart DESC) AS rownumber
					,EventLibrary.EventId
					,EventLibrary.Name
					,EventLibrary.EventType
					,EventLibrary.TimeStart
					,Eventlibrary.TimeEnd
				FROM EventLibrary)
			  AS ev 
			  INNER JOIN EventInfo AS ei
			  ON ev.EventId=ei.EventId
			  WHERE rownumber >= @beginRow AND rownumber <= @endRow
	END
GO


--Procedure Search Event

CREATE PROCEDURE sp_SearchEventLibrary
	@beginRow int,
	@endRow int,
	@username nvarchar(50),
	@parameter varchar(20)
	AS
	BEGIN
		SELECT ev.EventId 
			  ,ev.Name
			  ,ei.EventShortDesc
			  ,ev.EventType
			  ,ev.TimeStart
			  ,ev.TimeEnd 
			   ,ei.AvatarImg
			  ,ei.Created 
			  ,Case When (Select EventRegistration.Username from EventRegistration Where EventRegistration.EventId= ev.EventId AND EventRegistration.Username=@Username) =@Username 
			   THEN 'Joined' ELSE 'Join'
			   END As [Status]
			  ,Case When DATEDIFF(DAY,(Select EventInfo.Created from EventInfo Where EventInfo.EventId= ev.EventId),GETDATE ())<2
			   THEN 'new' ELSE 'likenew'
			   END As [type]
			   ,(SELECT COUNT(*) FROM EventComment evc WHERE evc.EventId=ev.EventId) AS commentCount
			   ,(SELECT COUNT(er.Username) FROM EventRegistration er WHERE er.EventId=ev.EventId ) AS joinedCount
			   
			   FROM(SELECT ROW_NUMBER() OVER(ORDER BY TimeStart DESC,EventId DESC) AS rownumber
					,EventLibrary.EventId
					,EventLibrary.Name
					,EventLibrary.EventType
					,EventLibrary.TimeStart
					,Eventlibrary.TimeEnd
				FROM EventLibrary
				WHERE EventId LIKE '%'+ @parameter
					   OR Name LIKE '%'+ @parameter +'%'
					   OR EventType LIKE '%'+ @parameter +'%'
					   OR TimeStart LIKE '%'+ @parameter 
					   OR TimeEnd	LIKE '%'+ @parameter  )
			  AS ev  
			  INNER JOIN EventInfo AS ei
			  ON ev.EventId=ei.EventId
		WHERE  rownumber >= @beginRow AND rownumber <= @endRow
	END
GO
--End Procedure Search Event
-- return count the  event search
CREATE PROCEDURE sp_getCountRecordSearch
	@parameter varchar(20)
	AS
		
	BEGIN
		SELECT COUNT(*)  AS CountSearch FROM EventLibrary
		WHERE EventId LIKE '%'+ @parameter 
					   OR Name LIKE '%'+ @parameter +'%'
					   OR EventType LIKE '%'+ @parameter +'%'
					   OR TimeStart LIKE '%'+ @parameter 
					   OR TimeEnd	LIKE '%'+ @parameter 
	END
GO




--Procedure get EventComment by page
CREATE PROCEDURE sp_getEventCommentPaging 
	@beginRow int,
	@endRow int,
	@eventId int
	AS
	BEGIN
		SELECT evc.Id
			  ,evc.EventId
			  ,evc.Username
			  ,evc.Comment
			  ,evc.Commented
			  ,em.Avatar
		FROM(SELECT ROW_NUMBER() OVER(ORDER BY Commented DESC,Id DESC ) AS rownumber
			  ,EventComment.Id
			  ,EventComment.EventId
			  ,EventComment.Username
			  ,EventComment.Comment
			  ,EventComment.Commented
	    FROM EventComment WHERE EventComment.EventId=@eventId)
		AS evc
		INNER JOIN Employee AS em
		ON em.Username=evc.Username
		WHERE rownumber>=@beginRow AND rownumber<=@endRow
	END
GO

CREATE PROCEDURE sp_getCountCommentByEventId
	@eventId int
	AS
	BEGIN
		SELECT EventId  FROM EventComment WHERE EventId=@eventId
	END
GO


-------------------------------------Notification
CREATE PROC sp_checkNotification
AS 
BEGIN
     ---INSERT INTO BIRTHDAY
     INSERT INTO [Blue_Pumpkin].[dbo].[Notifications] (
	 AvatarImg,
	 Fullname,
	 UsernameME,
	 UsernameFE,
	 Birthdate ,
	 NotificationTypeID)
	 SELECT 
	 e.Avatar,
	 e.Fullname,
	 b.Username,	
	 e.Username,
	 e.Birthdate ,
	 2
	 FROM [Blue_Pumpkin].[dbo].Employee AS e  CROSS JOIN [Blue_Pumpkin].[dbo].[Employee] AS b 
	 WHERE DATEPART(d, e.Birthdate) = DATEPART(d,GETDATE())
	 AND DATEPART(m, e.Birthdate) = DATEPART(m,GETDATE()) ;

	 --INSERT EVENT OFFiNG
	 INSERT INTO [Blue_Pumpkin].[dbo].[Notifications] (
	 UsernameME,
	 EventId,
	 NotificationTypeID,
	 NameEvent) 
	 SELECT 
	 er.Username,
	 e.EventId,
	 3,
	 e.Name 
	 FROM [Blue_Pumpkin].[dbo].EventLibrary AS e 
	 LEFT JOIN  [Blue_Pumpkin].[dbo].EventRegistration AS er
	 ON e.EventId = er.EventId 
	 WHERE DATEDIFF(DAY,GETDATE(), e.TimeStart) =1 AND er.Username IS NOT NULL
	  AND(SELECT Count (*) FROM Notifications AS n WHERE n.EventId = e.EventId AND n.NotificationTypeID =3) = 0;
	   
END
GO

CREATE PROC sp_checkNotification1

AS 
BEGIN
--INSERT EVENT NEW
   
    INSERT INTO [Blue_Pumpkin].[dbo].[Notifications] (
	 UsernameME,
	 NameEvent,
	 EventId,
	 NotificationTypeID,
	 AvatarImg
	 )
	 SELECT 
	  b.Username,
	  e.Name,
	  e.EventId,
	  1,
	  ei.AvatarImg
	  FROM [Blue_Pumpkin].[dbo].EventLibrary AS e   
	  LEFT JOIN EventInfo AS ei 
	  ON e.EventId  = ei.EventId
	  CROSS JOIN [Blue_Pumpkin].[dbo].[Employee] AS b
      WHERE (SELECT Count (*) FROM Notifications AS n WHERE n.EventId = e.EventId AND n.NotificationTypeID =1) = 0   
	  AND DATEDIFF(DAY,ei.Created,GETDATE ()) <2;
END
GO

CREATE PROC sp_getNotification
@NotificationTypeID int,
@Username varchar(50)
AS
BEGIN
    if(@NotificationTypeID = 1)
         SELECT * FROM Notifications AS N WHERE N.[Status] = 'N'AND N.NotificationTypeID =1 AND N.UsernameME= @Username
    else if (@NotificationTypeID = 2)
         SELECT * FROM Notifications AS N WHERE N.[Status] = 'N'AND N.NotificationTypeID =2 AND N.UsernameME= @Username
    else if (@NotificationTypeID = 3)
	     SELECT * FROM Notifications AS N WHERE N.[Status] = 'N'AND N.NotificationTypeID =3 AND N.UsernameME= @Username
	else if (@NotificationTypeID = 4)
	      SELECT * FROM Notifications AS N WHERE N.[Status] ='N'AND N.NotificationTypeID =4 AND N.UsernameME= @Username
	else if (@NotificationTypeID = 5)
       	SELECT * FROM Notifications AS N WHERE N.[Status]   ='N'AND N.NotificationTypeID =5 AND N.UsernameFE= @Username
    else if (@NotificationTypeID = 6)
       	SELECT * FROM Notifications AS N WHERE N.[Status]   ='N'AND N.NotificationTypeID =6 AND N.UsernameME= @Username
	 else if (@NotificationTypeID = 7)
       	SELECT * FROM Notifications AS N WHERE N.[Status]   ='N'AND N.NotificationTypeID =7 AND N.UsernameME= @Username
END

GO
sp_getNotification 1,'tuannv2'

EXEC sp_getNotification 1,'chungnd'

CREATE PROC sp_Birthdaywishes
@UsernameFE varchar(50),
@AvatarImg nvarchar(200) ,
@Fullname nvarchar(50),
@Message nvarchar(600)
AS
BEGIN 
    INSERT INTO Notifications (NotificationTypeID,UsernameFE,AvatarImg,Fullname,[Message])
	 VALUES(4,@UsernameFE,@AvatarImg,@Fullname,@Message)
END
GO

CREATE PROC sp_Eventinvitations
@UsernameME varchar(50),
@UsernameFE varchar(50),
@AvatarImg nvarchar(200),
@Fullname nvarchar(50),
@EventId int
AS 
BEGIN 
   INSERT INTO Notifications (UsernameME,UsernameFE,AvatarImg,Fullname,EventId) 
   VALUES(@UsernameME,@UsernameFE,@AvatarImg,@Fullname,@EventId)
END
GO
CREATE PROC sp_ChangeStatus
@Id int
AS  
BEGIN
    UPDATE Notifications SET Notifications.[Status] = 'Y' WHERE Notifications.Id = @Id
END
GO


CREATE PROC sp_sp_checkNotificationComment
@EventId int
AS
BEGIN 
    INSERT INTO [Blue_Pumpkin].[dbo].[Notifications] (
	 UsernameME,
	 NameEvent,
	 EventId,
	 NotificationTypeID
	 )
	 SELECT DISTINCT
	 EC.Username,
	 El.Name,
	 EC.EventId,
	 7
	 FROM EventComment AS EC
	 LEFT JOIN EventLibrary AS EL
	 ON EC.EventId = EL.EventId
	 WHERE EC.EventId  =@EventId
END
GO
-------------------------End Notification

CREATE PROCEDURE sp_InvitedAttendEvent
	@eventId int,
	@username nvarchar(50)
	AS
	BEGIN
		SELECT DISTINCT 
			em.Avatar
			,em.Username
			,CASE WHEN (Select EventRegistration.Username FROM EventRegistration WHERE EventRegistration.EventId= @eventId AND EventRegistration.Username=em.Username) =em.Username
			   THEN 'Joined' ELSE 'Invite' END AS [S-tatus]	
	    FROM Employee AS em
		WHERE em.Username<>@username
		
	END
GO

--------------Create proc sp_getListEventLibraryRelated
CREATE PROCEDURE [dbo].[sp_getListEventLibraryRelated]
	@eventType nvarchar(100)

	AS
	BEGIN
		SELECT ev.EventId
			  ,ev.Name
			  ,ev.[Description]
			  ,ev.EventType
			  ,ev.TimeStart
			  ,ev.TimeEnd
			  ,(select COUNT(*) FROM EventComment evc where evc.EventId=ev.EventId) AS commentCount
	    FROM EventLibrary AS ev
		
	    WHERE EventType=@eventType
	END
GO
--------------------------------Create store procedure get Event Details by eventId
CREATE PROCEDURE sp_getEventDetailsById
		@eventId int,
		@username nvarchar(20)
	AS
	BEGIN
		SELECT ev.EventId
			  ,ev.Name
			  ,ev.Description
			  ,ev.EventType
			  ,ev.TimeStart
			  ,ev.TimeEnd
			  ,ei.AvatarImg
			  ,ei.Created 
			  ,Case When (Select EventRegistration.Username from EventRegistration Where EventRegistration.EventId= ev.EventId AND EventRegistration.Username=@username) =@username 
			   THEN 'Joined' ELSE 'Join'
			   END As [Status]
			  ,Case When DATEDIFF(DAY,(Select EventInfo.Created from EventInfo Where EventInfo.EventId= ev.EventId),GETDATE ())<2
			   THEN 'new' ELSE 'likenew'
			   END As [type]
			   ,(SELECT COUNT(*) FROM EventComment evc WHERE evc.EventId=ev.EventId) AS commentCount
			   ,(SELECT COUNT(*) FROM EventRegistration er WHERE er.EventId=ev.EventId ) AS joinedCount
			   
			  FROM EventLibrary AS ev 
			  INNER JOIN EventInfo AS ei
			  ON ev.EventId=ei.EventId
			  WHERE ev.EventId=@eventId
	END
GO

------------------------------------------------------------
CREATE  PROCEDURE sp_GetEventRegistedByUser
	@beginRow int,
	@endRow int,
	@Username nvarchar(50)
	AS
	BEGIN
		SELECT er.EventId
			  ,el.Name
			  ,er.TimeRegisted			  
			   FROM(SELECT ROW_NUMBER() OVER(ORDER BY TimeRegisted DESC) AS rownumber
					,[EventRegistration].EventId
					,[EventRegistration].TimeRegisted
				FROM [EventRegistration] WHERE EventRegistration.username = @Username )
			  AS er
			  INNER JOIN EventLibrary AS el
			  ON er.EventId=el.EventId
			  WHERE rownumber >= @beginRow AND rownumber <= @endRow
	END
GO

CREATE  PROCEDURE sp_GetEventCommentByUser
	@beginRow int,
	@endRow int,
	@Username nvarchar(50)
	AS
	BEGIN
		SELECT ec.EventId
			  ,el.Name
			  ,ec.Comment
			  ,ec.Commented		  
			   FROM(SELECT ROW_NUMBER() OVER(ORDER BY Commented DESC) AS rownumber
					,[EventComment].EventId
					,[EventComment].Comment
					,[EventComment].Commented
				FROM [EventComment] WHERE EventComment.username = @Username )
			  AS ec
			  INNER JOIN EventLibrary AS el
			  ON ec.EventId=el.EventId
			  WHERE rownumber >= @beginRow AND rownumber <= @endRow
	END
GO



CREATE PROCEDURE sp_getUpcomingEvent
	@Username nvarchar(20)
	AS
	BEGIN
		SELECT TOP 1 ev.EventId
			  ,ev.Name
			  ,ei.EventShortDesc
			  ,ev.EventType
			  ,ev.TimeStart
			  ,ev.TimeEnd
			  ,ei.AvatarImg
			  ,ei.Created 
			  ,Case When (Select EventRegistration.Username from EventRegistration Where EventRegistration.EventId= ev.EventId AND EventRegistration.Username=@Username) =@Username 
			   THEN 'Joined' ELSE 'Join'
			   END As [Status]
			  ,CASE WHEN DATEDIFF(DAY,(ev.TimeStart),GETDATE ())>=0 
						  AND DATEDIFF(MONTH,(ev.TimeStart),GETDATE ())>=0
						  AND DATEDIFF(YEAR,(ev.TimeStart),GETDATE ())>=0 
						   AND DATEDIFF(HOUR,(ev.TimeStart),GETDATE ())>=0
						  AND DATEDIFF(MINUTE,(ev.TimeStart),GETDATE ())>=0  
						  THEN 'Upcoming' 
			   END AS Status2
			  ,Case When DATEDIFF(DAY,(Select EventInfo.Created from EventInfo Where EventInfo.EventId= ev.EventId),GETDATE ())<2
			   THEN 'new' ELSE 'likenew'
			   END As [type]
			   ,(SELECT COUNT(*) FROM EventComment evc WHERE evc.EventId=ev.EventId) AS commentCount
			   ,(SELECT COUNT(*) FROM EventRegistration er WHERE er.EventId=ev.EventId ) AS joinedCount
			   
			   FROM EventLibrary   AS ev  
			   
			  INNER JOIN EventInfo AS ei
			  ON ev.EventId=ei.EventId

			 
			  ORDER BY ev.TimeStart ASC
	END
GO
CREATE PROCEDURE sp_SendInvitedAttendEvent
	@usernameME varchar(50),
	@usernameFE varchar(50),
	@messages nvarchar(50),
	@eventId int
	AS 
	BEGIN 
   INSERT INTO Notifications (NotificationTypeID,UsernameME,UsernameFE,Message,EventId) 
   VALUES(5,@usernameME,@usernameFE,@messages,@eventId)
END

CREATE PROCEDURE sp_PostComment
	@eventId int,
	@username nvarchar(50),
	@comment nvarchar(500)
	AS
	BEGIN
		INSERT INTO EventComment(EventId,Username,Comment,Commented) VALUES(@eventId,@username,@comment,GETDATE())
	END
GO
